#!/usr/bin/env python3
"""
检查MySQL数据库中的执行计划数据
"""

import pymysql
from datetime import datetime

def check_mysql_data():
    """检查MySQL数据库中的执行计划数据"""
    print("🔍 检查MySQL数据库中的执行计划数据")
    print("=" * 60)
    
    try:
        # 连接MySQL数据库
        conn = pymysql.connect(
            host="localhost",
            user="root",
            password="123456",
            database="kylin_2025",
            charset="utf8mb4"
        )
        
        with conn.cursor() as cursor:
            # 1. 检查表是否存在
            print("📋 检查表结构...")
            cursor.execute("SHOW TABLES LIKE 'execution_plans'")
            tables = cursor.fetchall()
            if tables:
                print("✅ execution_plans 表存在")
            else:
                print("❌ execution_plans 表不存在")
                return
            
            # 2. 查看表结构
            print("\n📊 表结构:")
            cursor.execute("DESCRIBE execution_plans")
            columns = cursor.fetchall()
            for column in columns:
                print(f"   - {column[0]}: {column[1]}")
            
            # 3. 统计记录数量
            print(f"\n📈 数据统计:")
            cursor.execute("SELECT COUNT(*) FROM execution_plans")
            result = cursor.fetchone()
            total_count = result[0] if result else 0
            print(f"   总记录数: {total_count}")
            
            # 4. 查看最近的记录
            print(f"\n📝 最近的执行计划记录:")
            cursor.execute("""
                SELECT plan_id, problem_type, severity, risk_level, created_at 
                FROM execution_plans 
                ORDER BY created_at DESC 
                LIMIT 5
            """)
            recent_plans = cursor.fetchall()
            
            if recent_plans:
                for plan in recent_plans:
                    print(f"   - {plan[0]}: {plan[1]} (严重程度: {plan[2]}, 风险: {plan[3]}, 时间: {plan[4]})")
            else:
                print("   暂无记录")
            
            # 5. 查看详细的一条记录
            if recent_plans:
                print(f"\n🔍 查看详细记录 (第一条):")
                plan_id = recent_plans[0][0]
                cursor.execute("SELECT * FROM execution_plans WHERE plan_id = %s", (plan_id,))
                detail = cursor.fetchone()
                
                if detail:
                    print(f"   计划ID: {detail[0]}")
                    print(f"   问题类型: {detail[1]}")
                    print(f"   严重程度: {detail[2]}")
                    print(f"   描述: {detail[3]}")
                    print(f"   影响服务: {detail[4]}")
                    print(f"   建议操作: {detail[5]}")
                    print(f"   置信度: {detail[6]}")
                    print(f"   推理过程: {detail[7]}")
                    print(f"   创建时间: {detail[8]}")
                    print(f"   任务JSON: {detail[9][:100]}..." if detail[9] else "   任务JSON: 空")
                    print(f"   执行顺序: {detail[10]}")
                    print(f"   预计总时间: {detail[11]}秒")
                    print(f"   风险等级: {detail[12]}")
            
            # 6. 按问题类型统计
            print(f"\n📊 按问题类型统计:")
            cursor.execute("""
                SELECT problem_type, COUNT(*) as count 
                FROM execution_plans 
                GROUP BY problem_type 
                ORDER BY count DESC
            """)
            type_stats = cursor.fetchall()
            
            for stat in type_stats:
                print(f"   - {stat[0]}: {stat[1]} 条记录")
            
            # 7. 按风险等级统计
            print(f"\n📊 按风险等级统计:")
            cursor.execute("""
                SELECT risk_level, COUNT(*) as count 
                FROM execution_plans 
                GROUP BY risk_level 
                ORDER BY count DESC
            """)
            risk_stats = cursor.fetchall()
            
            for stat in risk_stats:
                print(f"   - {stat[0]}: {stat[1]} 条记录")
        
        conn.close()
        print(f"\n✅ 数据库检查完成")
        
    except Exception as e:
        print(f"❌ 数据库检查失败: {e}")

def test_insert_sample_data():
    """测试插入示例数据"""
    print(f"\n🧪 测试插入示例数据")
    print("=" * 60)
    
    try:
        conn = pymysql.connect(
            host="localhost",
            user="root",
            password="123456",
            database="kylin_2025",
            charset="utf8mb4"
        )
        
        with conn.cursor() as cursor:
            # 插入测试数据
            test_plan = {
                "plan_id": f"test_plan_{datetime.now().strftime('%Y%m%d_%H%M%S')}",
                "problem_type": "test_problem",
                "severity": 6.5,
                "description": "这是一个测试执行计划",
                "affected_services": '["service1", "service2"]',
                "suggested_actions": '["action1", "action2"]',
                "confidence": 0.85,
                "reasoning": "测试推理过程",
                "created_at": datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                "tasks_json": '[{"task_id": "task1", "description": "测试任务"}]',
                "execution_order_json": '["task1"]',
                "estimated_total_time": 300,
                "risk_level": "medium"
            }
            
            sql = '''
            INSERT INTO execution_plans (
                plan_id, problem_type, severity, description, affected_services,
                suggested_actions, confidence, reasoning, created_at,
                tasks_json, execution_order_json, estimated_total_time, risk_level
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            '''
            
            cursor.execute(sql, (
                test_plan["plan_id"],
                test_plan["problem_type"],
                test_plan["severity"],
                test_plan["description"],
                test_plan["affected_services"],
                test_plan["suggested_actions"],
                test_plan["confidence"],
                test_plan["reasoning"],
                test_plan["created_at"],
                test_plan["tasks_json"],
                test_plan["execution_order_json"],
                test_plan["estimated_total_time"],
                test_plan["risk_level"]
            ))
            
            print(f"✅ 测试数据插入成功: {test_plan['plan_id']}")
        
        conn.close()
        
    except Exception as e:
        print(f"❌ 测试数据插入失败: {e}")

def main():
    """主函数"""
    print("🔍 MySQL数据库数据检查工具")
    print("=" * 60)
    
    # 检查现有数据
    check_mysql_data()
    
    # 询问是否插入测试数据
    print(f"\n" + "=" * 60)
    response = input("是否插入测试数据来验证数据库功能? (y/n): ")
    if response.lower() == 'y':
        test_insert_sample_data()
        print(f"\n重新检查数据...")
        check_mysql_data()

if __name__ == "__main__":
    main() 